package net.skyedu.dao;

import java.io.PrintStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import net.skyedu.bean.Appeal;
import net.skyedu.bean.AppealTime;
import net.skyedu.bean.Attendance;
import net.skyedu.bean.Departments;
import net.skyedu.bean.Detail;
import net.skyedu.bean.Shensu;
import net.skyedu.bean.ShowUser;
import net.skyedu.bean.SumLate;
import net.skyedu.bean.UserInfo;
import net.skyedu.bean.UserList;
import net.skyedu.utils.DateUtil;

public class UserInfoDao extends DAO
{
  private Connection conn = null;
  private PreparedStatement pstmt = null;
  private ResultSet rs = null;
  private DateUtil du = null;

  public int addAppeal(Appeal app)
  {
    StringBuffer sql = new StringBuffer("insert into appeal(app_att_id,app_sendname,app_recipientid,app_content,app_createtime) values(?,?,?,?,getdate())");
    String[] params = { Integer.toString(app.getAppattid()), app.getSendname(), Integer.toString(app.getRecipientid()), app.getContent() };
    return executeSQL(sql.toString(), params);
  }

  public List<Departments> getAllSch() {
    List schlist = new ArrayList();
    Departments depa = null;
    try {
      StringBuffer sql = new StringBuffer("select * from DEPARTMENTS where DEPTID not in (1,-1)");

      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        depa = new Departments();
        depa.setDeptId(this.rs.getInt("deptid"));
        depa.setDeptName(this.rs.getString("deptname"));
        schlist.add(depa);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return schlist;
  }

  public ShowUser findSpec(int schId, String badg, String username, String startTime) {
    ShowUser att = null;
    try {
      StringBuffer sql = new StringBuffer("select u.USERID,u.Name,d.DEPTNAME,a.* from USERINFO u,Attendance a,DEPARTMENTS d where u.USERID=a.att_user_id and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) and u.DEFAULTDEPTID=" + 
        schId + " ");

      if ((badg != null) && (!"".equals(badg))) {
        sql.append("and u.Badgenumber='%" + badg + "%' ");
      }
      if ((username != null) && (!"".equals(username))) {
        sql.append("and u.Name like '%" + username + "%' ");
      }
      if ((startTime != null) && (!"".equals(startTime))) {
        sql.append("and a.att_date = '" + startTime + "' ");
      }
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        att = new ShowUser();
        att.setAttid(this.rs.getInt("att_id"));
        att.setUserId(this.rs.getInt("USERID"));
        att.setBadgenumber(this.rs.getString("Badgenumber"));
        att.setUsername(this.rs.getString("Name"));
        att.setDeptname(this.rs.getString("DEPTNAME"));
        att.setSubdept(this.rs.getString("SubDept"));
        att.setAttdate(this.rs.getString("att_date"));
        att.setAttworktime(this.rs.getString("att_worktime"));
        att.setAttclosingtime(this.rs.getString("att_closingtime"));
        att.setAttstate(this.rs.getInt("att_state"));
        att.setAttweek(this.rs.getInt("att_week"));
        att.setAttlatetime(this.rs.getInt("att_latetime"));
        att.setAttearlytime(this.rs.getInt("att_earlytime"));
        att.setAttconfirm(this.rs.getInt("att_confirm"));
        att.setAttupdatestate(this.rs.getInt("att_updatestate"));
        att.setAttdesc(this.rs.getString("att_desc"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return att;
  }

  public List<Appeal> getAttendance(int pageSize, int startRow, int userId, String startTime, String endTime)
  {
    List applist = new ArrayList();
    Appeal app = null;
    try {
      StringBuffer sql1 = new StringBuffer("and app_id not in(select top " + startRow + " app_id from appeal where app_recipientid=" + userId + " ");
      StringBuffer sql = new StringBuffer("select top " + pageSize + " * from appeal where app_recipientid=" + userId + " ");
      if ((startTime != null) && (!"".equals(startTime)) && (endTime != null) && (!"".equals(endTime))) {
        sql.append("and app_createtime between '" + startTime + "' and '" + endTime + "' ");
        sql1.append("and app_createtime between '" + startTime + "' and '" + endTime + "' ");
      }
      sql.append(sql1).append("order by app_createtime desc) order by app_createtime desc");
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        app = new Appeal();
        app.setAppid(this.rs.getInt("app_id"));
        app.setAppattid(this.rs.getInt("app_att_id"));
        app.setSendname(this.rs.getString("app_sendname"));
        app.setCreatetime(this.rs.getString("app_createtime").substring(0, 19));
        app.setContent(this.rs.getString("app_content"));
        app.setStatus(this.rs.getInt("app_status"));
        app.setType(this.rs.getInt("app_type"));
        app.setReply(this.rs.getString("app_reply"));
        app.setReplytime(this.rs.getString("app_replytime"));
        applist.add(app);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return applist;
  }

  public int getAppCount(int recipientid, String startTime, String endTime)
  {
    int num = 0;
    try {
      StringBuffer sql = new StringBuffer("select COUNT(*) from appeal where app_recipientid=" + recipientid + " ");
      if ((startTime != null) && (!"".equals(startTime)) && (endTime != null) && (!"".equals(endTime)))
        sql.append("and app_createtime between '" + startTime + "' and '" + endTime + "'");
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next())
        num = this.rs.getInt(1);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return num;
  }

  public Appeal getRecip(int appId)
  {
    Appeal app = null;
    try {
      StringBuffer sql = new StringBuffer("select * from appeal where app_id=" + appId);
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        app = new Appeal();
        app.setAppid(this.rs.getInt("app_id"));
        app.setRecipientid(this.rs.getInt("app_recipientid"));
        app.setAppattid(this.rs.getInt("app_att_id"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return app;
  }

  public Departments getSchByname(int userId)
  {
    Departments de = null;
    try {
      StringBuffer sql = new StringBuffer("select d.deptname,o.Name from userinfo o,DEPARTMENTS d where o.defaultdeptid=d.deptid and o.USERID='" + userId + "'");
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        de = new Departments();
        de.setUserName(this.rs.getString("Name"));
        de.setDeptName(this.rs.getString("deptname"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return de;
  }

  public UserInfo getSSN(int userId)
  {
    UserInfo user = null;
    try {
      StringBuffer sql = new StringBuffer("select * from USERINFO where USERID=" + userId);
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        user = new UserInfo();
        user.setBadgeNumber(this.rs.getString("badgeNumber"));
        user.setSsn(this.rs.getString("ssn"));
        user.setName(this.rs.getString("Name"));
        user.setStatus(this.rs.getInt("status"));
        user.setDefaultDeptId(this.rs.getInt("DEFAULTDEPTID"));
        user.setGender(this.rs.getString("Gender"));
        user.setSubDept(this.rs.getString("SubDept"));
        user.setOaId(this.rs.getInt("tea_id"));
        user.setPostId(this.rs.getInt("post_id"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return user;
  }

  public UserInfo getSSN1(int userId) {
    UserInfo user = null;
    try {
      StringBuffer sql = new StringBuffer("select * from USERINFO where tea_id=" + userId);
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        user = new UserInfo();
        user.setBadgeNumber(this.rs.getString("badgeNumber"));
        user.setSsn(this.rs.getString("ssn"));
        user.setName(this.rs.getString("Name"));
        user.setUserId(this.rs.getInt("USERID"));
        user.setOaId(this.rs.getInt("tea_id"));
        user.setPostId(this.rs.getInt("post_id"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return user;
  }

  public List<ShowUser> showMyAttend(int pageSize, int startRow, int userId, int sta) {
    List userList = new ArrayList();
    ShowUser su = null;
    try {
      StringBuffer sql1 = new StringBuffer("and a.att_id not in (select top " + startRow + " a.att_id from Attendance a,USERINFO u,DEPARTMENTS d " + 
        "where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) and u.status=1 and u.USERID=" + userId + " ");
      StringBuffer sql = new StringBuffer("select top " + pageSize + " u.USERID,d.DEPTNAME,u.SubDept,a.* from Attendance a,USERINFO u,DEPARTMENTS d " + 
        "where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) and u.status=1 and u.USERID=" + userId + " ");

      if (sta == 1) {
        sql.append("and a.att_date between (select CONVERT(varchar(100), dateadd(d,-day(getdate())+1,getdate()), 23)) and (select CONVERT(varchar(100), dateadd(dd,-day(dateadd(month,1,getdate())),dateadd(month,1,getdate())), 23)) ");
        sql1.append("and a.att_date between (select CONVERT(varchar(100), dateadd(d,-day(getdate())+1,getdate()), 23)) and (select CONVERT(varchar(100), dateadd(dd,-day(dateadd(month,1,getdate())),dateadd(month,1,getdate())), 23)) ");
      }
      if (sta == 2) {
        sql.append("and a.att_date between (select CONVERT(varchar(100), dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())), 23)) and (select CONVERT(varchar(100), dateadd(dd,-day(getdate()),getdate()), 23)) ");
        sql1.append("and a.att_date between (select CONVERT(varchar(100), dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())), 23)) and (select CONVERT(varchar(100), dateadd(dd,-day(getdate()),getdate()), 23)) ");
      }
      if (sta == 3) {
        sql.append("and a.att_date between (select CONVERT(varchar(100),dateadd(mm,-3,getdate()),23)) and (select CONVERT(varchar(100),getdate(),23)) ");
        sql1.append("and a.att_date between (select CONVERT(varchar(100),dateadd(mm,-3,getdate()),23)) and (select CONVERT(varchar(100),getdate(),23)) ");
      }

      sql.append(sql1).append("order by a.att_date desc) order by a.att_date desc");

      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        su = new ShowUser();
        su.setAttid(this.rs.getInt("att_id"));
        su.setUserId(this.rs.getInt("USERID"));
        su.setDeptname(this.rs.getString("DEPTNAME"));
        su.setSubdept(this.rs.getString("SubDept"));
        su.setAttdate(this.rs.getString("att_date"));
        su.setAttweek(this.rs.getInt("att_week"));
        su.setAttworktime(this.rs.getString("att_worktime"));
        su.setAttclosingtime(this.rs.getString("att_closingtime"));
        su.setAttstate(this.rs.getInt("att_state"));
        su.setAttlatetime(this.rs.getInt("att_latetime"));
        su.setAttearlytime(this.rs.getInt("att_earlytime"));
        su.setAttconfirm(this.rs.getInt("att_confirm"));
        su.setAttdesc(this.rs.getString("att_desc"));
        userList.add(su);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return userList;
  }

  public int getCountMyAttend(int userId, int sta)
  {
    int num = 0;
    this.du = new DateUtil();
    Map map = this.du.findThisMonth();
    Map map1 = this.du.findLastMonth();
    Map map2 = this.du.findThreeMonthsAgo();
    try {
      StringBuffer sql = new StringBuffer("select COUNT(*) from Attendance a,USERINFO u,DEPARTMENTS d where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) and u.status=1 and u.USERID = " + 
        userId + " ");
      String sqls = "";
      if (sta == 1) {
        sqls = "and a.att_date between '" + (String)map.get("beginTime") + "' and '" + (String)map.get("endTime") + "' ";
        sql.append(sqls);
      }
      if (sta == 2) {
        sqls = "and a.att_date between '" + (String)map1.get("prevMonthFD") + "' and '" + (String)map1.get("prevMonthPD") + "' ";
        sql.append(sqls);
      }
      if (sta == 3) {
        sqls = "and a.att_date between '" + (String)map2.get("threeMonthsAgoDate") + "' and '" + (String)map2.get("nowDate") + "' ";
        sql.append(sqls);
      }

      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next())
        num = this.rs.getInt(1);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return num;
  }

  public SumLate getSum(int userId, int sta) {
    SumLate su = null;
    this.du = new DateUtil();
    Map map = this.du.findThisMonth();
    Map map1 = this.du.findLastMonth();
    Map map2 = this.du.findThreeMonthsAgo();
    try {
      StringBuffer sql = new StringBuffer("select sum(att_latetime) su from Attendance where att_user_id = " + userId + " ");
      String sqls = "";
      if (sta == 1) {
        sqls = "and att_date between '" + (String)map.get("beginTime") + "' and '" + (String)map.get("endTime") + "' ";
        sql.append(sqls);
      }
      if (sta == 2) {
        sqls = "and att_date between '" + (String)map1.get("prevMonthFD") + "' and '" + (String)map1.get("prevMonthPD") + "' ";
        sql.append(sqls);
      }
      if (sta == 3) {
        sqls = "and att_date between '" + (String)map2.get("threeMonthsAgoDate") + "' and '" + (String)map2.get("nowDate") + "' ";
        sql.append(sqls);
      }

      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        su = new SumLate();
        su.setLateSum(this.rs.getInt("su"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return su;
  }

  public SumLate getSum(String username, String startTime, String endTime, int sta) {
    SumLate su = null;
    this.du = new DateUtil();
    Map map = this.du.findThisMonth();
    Map map1 = this.du.findLastMonth();
    Map map2 = this.du.findThreeMonthsAgo();
    try {
      StringBuffer sql = new StringBuffer("select sum(a.att_latetime) su from USERINFO u ,Attendance a where u.USERID=a.att_user_id and u.Name like '%" + username + "%' ");
      String sqls = "";
      if (sta == 1) {
        sqls = "and a.att_date between '" + (String)map.get("beginTime") + "' and '" + (String)map.get("endTime") + "' ";
        sql.append(sqls);
      }
      if (sta == 2) {
        sqls = "and a.att_date between '" + (String)map1.get("prevMonthFD") + "' and '" + (String)map1.get("prevMonthPD") + "' ";
        sql.append(sqls);
      }
      if (sta == 3) {
        sqls = "and a.att_date between '" + (String)map2.get("threeMonthsAgoDate") + "' and '" + (String)map2.get("nowDate") + "' ";
        sql.append(sqls);
      }

      if ((startTime != null) && (!"".equals(startTime)) && (endTime != null) && (!"".equals(endTime))) {
        sql.append("and a.att_date between '" + startTime + "' and '" + endTime + "' ");
      }
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      System.out.println(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        su = new SumLate();
        su.setLateSum(this.rs.getInt("su"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return su;
  }

  public List<ShowUser> getAttBySchName(String isqianhai, int userid,int pageSize, int startRow, int deptId, String username, String startTime, String endTime, int state, int sta) {
    List attlist = new ArrayList();
    ShowUser att = null;
    this.du = new DateUtil();
    Map map = this.du.findThisMonth();
    Map map1 = this.du.findLastMonth();
    Map map2 = this.du.findThreeMonthsAgo();
    try {
      StringBuffer sql1 = new StringBuffer("and a.att_id not in(select top " + startRow + " a.att_id from Attendance a,USERINFO u,DEPARTMENTS d " + 
        "where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) and u.status=1 and d.deptID='"+deptId+"'");

      if("1".equals(isqianhai))
      {
    	  sql1 = new StringBuffer("and a.att_id not in(select top " + startRow + " a.att_id from Attendance a,USERINFO u,DEPARTMENTS d " + 
    		        "where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) and u.status=1 and d.deptID='13'");

    		    
      }
//      if (userid == 1421)
//      {
//        sql1.append("and d.DEPTID in ('" + deptId + "','13') ");
//      }
//      else
//      {
//        sql1.append("and d.DEPTID='" + deptId + "' ");
//      }
      StringBuffer sql = new StringBuffer("select top " + pageSize + " u.USERID,u.Name,u.Badgenumber,d.DEPTNAME,u.SubDept,a.* from Attendance a,USERINFO u,DEPARTMENTS d " + 
        "where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) and u.status=1  and d.deptID='"+deptId+"'");
      if("1".equals(isqianhai))
      {
    	  sql = new StringBuffer("select top " + pageSize + " u.USERID,u.Name,u.Badgenumber,d.DEPTNAME,u.SubDept,a.* from Attendance a,USERINFO u,DEPARTMENTS d " + 
    		        "where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) and u.status=1  and d.deptID='13'");
    		    
      }
//      if (userid == 1421)
//      {
//        sql.append("and d.DEPTID in ('" + deptId + "','13') ");
//      }
//      else
//      {
//        sql.append("and d.DEPTID='" + deptId + "' ");
//      }
      if ((username != null) && (!"".equals(username))) {
        sql.append("and u.Name like '%" + username + "%' ");
        sql1.append("and u.Name like '%" + username + "%' ");
      }
      if (state != 0) {
        sql.append("and a.att_state=" + state + " ");
        sql1.append("and a.att_state=" + state + " ");
      }
      String sqls = "";
      if (sta == 1) {
        sqls = "and a.att_date between '" + (String)map.get("beginTime") + "' and '" + (String)map.get("endTime") + "' ";
        sql.append(sqls);
        sql1.append(sqls);
      }
      if (sta == 2) {
        sqls = "and a.att_date between '" + (String)map1.get("prevMonthFD") + "' and '" + (String)map1.get("prevMonthPD") + "' ";
        sql.append(sqls);
        sql1.append(sqls);
      }
      if (sta == 3) {
        sqls = "and a.att_date between '" + (String)map2.get("threeMonthsAgoDate") + "' and '" + (String)map2.get("nowDate") + "' ";
        sql.append(sqls);
        sql1.append(sqls);
      }

      if ((startTime != null) && (!"".equals(startTime)) && (endTime != null) && (!"".equals(endTime))) {
        sql.append("and a.att_date between '" + startTime + "' and '" + endTime + "' ");
        sql1.append("and a.att_date between '" + startTime + "' and '" + endTime + "' ");
      }
      sql.append(sql1).append("order by u.Name,a.att_date desc)order by u.Name ,a.att_date desc");
      System.out.println(sql);
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        att = new ShowUser();
        att.setAttid(this.rs.getInt("att_id"));
        att.setUserId(this.rs.getInt("USERID"));
        att.setBadgenumber(this.rs.getString("Badgenumber"));
        att.setUsername(this.rs.getString("Name"));
        att.setDeptname(this.rs.getString("DEPTNAME"));
        att.setSubdept(this.rs.getString("SubDept"));
        att.setAttdate(this.rs.getString("att_date"));
        att.setAttworktime(this.rs.getString("att_worktime"));
        att.setAttclosingtime(this.rs.getString("att_closingtime"));
        att.setAttstate(this.rs.getInt("att_state"));
        att.setAttweek(this.rs.getInt("att_week"));
        att.setAttlatetime(this.rs.getInt("att_latetime"));
        att.setAttearlytime(this.rs.getInt("att_earlytime"));
        att.setAttconfirm(this.rs.getInt("att_confirm"));
        att.setAttupdatestate(this.rs.getInt("att_updatestate"));
        att.setAttdesc(this.rs.getString("att_desc"));
        attlist.add(att);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return attlist;
  }

  public int getCountBySchName(String isqianhai,int userid,int deptId, String username, String startTime, String endTime, int state, int sta) {
    int num = 0;
    this.du = new DateUtil();
    Map map = this.du.findThisMonth();
    Map map1 = this.du.findLastMonth();
    Map map2 = this.du.findThreeMonthsAgo();
    try {
      StringBuffer sql = new StringBuffer("select COUNT(*) from Attendance a,USERINFO u,DEPARTMENTS d where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) and u.status=1 and d.deptid='"+deptId+"' ");

//      if (userid == 1421)
//      {
//        sql.append("and d.DEPTID in ('" + deptId + "','13') ");
//      }
//      else
//      {
//        sql.append("and d.DEPTID='" + deptId + "' ");
//      }
      if("1".equals(isqianhai))
      {
    	  sql = new StringBuffer("select COUNT(*) from Attendance a,USERINFO u,DEPARTMENTS d where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) and u.status=1 and d.deptid='13' ");

      }
      if ((username != null) && (!"".equals(username))) {
        sql.append("and u.Name like '%" + username + "%' ");
      }
      if (state != 0) {
        sql.append("and a.att_state=" + state + " ");
      }
      String sqls = "";
      if (sta == 1) {
        sqls = "and a.att_date between '" + (String)map.get("beginTime") + "' and '" + (String)map.get("endTime") + "' ";
        sql.append(sqls);
      }
      if (sta == 2) {
        sqls = "and a.att_date between '" + (String)map1.get("prevMonthFD") + "' and '" + (String)map1.get("prevMonthPD") + "' ";
        sql.append(sqls);
      }
      if (sta == 3) {
        sqls = "and a.att_date between '" + (String)map2.get("threeMonthsAgoDate") + "' and '" + (String)map2.get("nowDate") + "' ";
        sql.append(sqls);
      }

      if ((startTime != null) && (!"".equals(startTime)) && (endTime != null) && (!"".equals(endTime))) {
        sql.append("and a.att_date between '" + startTime + "' and '" + endTime + "' ");
      }
      System.out.println(sql);
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next())
        num = this.rs.getInt(1);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return num;
  }

  public List<ShowUser> getAttBySchId(int pageSize, int startRow, int deptId, String name, String startTime, String endTime, int state, int sta)
  {
    List attlist = new ArrayList();
    ShowUser att = null;
    this.du = new DateUtil();
    Map map = this.du.findThisMonth();
    Map map1 = this.du.findLastMonth();
    Map map2 = this.du.findThreeMonthsAgo();
    try {
      StringBuffer sql1 = new StringBuffer("and a.att_id not in(select top " + startRow + " a.att_id from Attendance a,USERINFO u,DEPARTMENTS d where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) ");

      StringBuffer sql = new StringBuffer("select top " + pageSize + " u.USERID,u.Name,u.Badgenumber,d.DEPTID,d.DEPTNAME,u.SubDept,a.* from Attendance a,USERINFO u,DEPARTMENTS d where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) ");
      if (deptId != 0) {
        sql.append("and d.DEPTID=" + deptId + " ");
        sql1.append("and d.DEPTID=" + deptId + " ");
      }
      if ((name != null) && (!"".equals(name))) {
        sql.append("and u.Name like '%" + name + "%' ");
        sql1.append("and u.Name like '%" + name + "%' ");
      }
      if (state != 0) {
        sql.append("and a.att_state=" + state + " ");
        sql1.append("and a.att_state=" + state + " ");
      }
      String sqls = "";
      if (sta == 1) {
        sqls = "and a.att_date between '" + (String)map.get("beginTime") + "' and '" + (String)map.get("endTime") + "' ";
        sql.append(sqls);
        sql1.append(sqls);
      }
      if (sta == 2) {
        sqls = "and a.att_date between '" + (String)map1.get("prevMonthFD") + "' and '" + (String)map1.get("prevMonthPD") + "' ";
        sql.append(sqls);
        sql1.append(sqls);
      }
      if (sta == 3) {
        sqls = "and a.att_date between '" + (String)map2.get("threeMonthsAgoDate") + "' and '" + (String)map2.get("nowDate") + "' ";
        sql.append(sqls);
        sql1.append(sqls);
      }

      if ((startTime != null) && (!"".equals(startTime)) && (endTime != null) && (!"".equals(endTime))) {
        sql.append("and a.att_date between '" + startTime + "' and '" + endTime + "' ");
        sql1.append("and a.att_date between '" + startTime + "' and '" + endTime + "' ");
      }

      sql.append(sql1).append("order by u.Name ,a.att_date desc) order by u.Name ,a.att_date desc");
      System.out.println(sql.toString());
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        att = new ShowUser();
        att.setAttid(this.rs.getInt("att_id"));
        att.setUserId(this.rs.getInt("USERID"));
        att.setUsername(this.rs.getString("Name"));
        att.setBadgenumber(this.rs.getString("Badgenumber"));
        att.setDeptid(this.rs.getInt("DEPTID"));
        att.setDeptname(this.rs.getString("DEPTNAME"));
        att.setSubdept(this.rs.getString("SubDept"));
        att.setAttdate(this.rs.getString("att_date"));
        att.setAttweek(this.rs.getInt("att_week"));
        att.setAttworktime(this.rs.getString("att_worktime"));
        att.setAttclosingtime(this.rs.getString("att_closingtime"));
        att.setAttstate(this.rs.getInt("att_state"));
        att.setAttlatetime(this.rs.getInt("att_latetime"));
        att.setAttearlytime(this.rs.getInt("att_earlytime"));
        att.setAttconfirm(this.rs.getInt("att_confirm"));
        att.setAttdesc(this.rs.getString("att_desc"));
        attlist.add(att);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return attlist;
  }

  public int getCountBySchID(int deptId, String name, String startTime, String endTime, int state, int sta)
  {
    int num = 0;
    this.du = new DateUtil();
    Map map = this.du.findThisMonth();
    Map map1 = this.du.findLastMonth();
    Map map2 = this.du.findThreeMonthsAgo();
    try {
      StringBuffer sql = new StringBuffer("select COUNT(*) from Attendance a,USERINFO u,DEPARTMENTS d where a.att_user_id=u.USERID and u.DEFAULTDEPTID=d.DEPTID and u.DEFAULTDEPTID not in (1,-1) ");

      if (deptId != 0) {
        sql.append("and d.DEPTID='" + deptId + "' ");
      }
      if ((name != null) && (!"".equals(name))) {
        sql.append("and u.Name like '%" + name + "%' ");
      }
      if (state != 0) {
        sql.append("and a.att_state=" + state + " ");
      }
      String sqls = "";
      if (sta == 1) {
        sqls = "and a.att_date between '" + (String)map.get("beginTime") + "' and '" + (String)map.get("endTime") + "' ";
        sql.append(sqls);
      }
      if (sta == 2) {
        sqls = "and a.att_date between '" + (String)map1.get("prevMonthFD") + "' and '" + (String)map1.get("prevMonthPD") + "' ";
        sql.append(sqls);
      }
      if (sta == 3) {
        sqls = "and a.att_date between '" + (String)map2.get("threeMonthsAgoDate") + "' and '" + (String)map2.get("nowDate") + "' ";
        sql.append(sqls);
      }

      if ((startTime != null) && (!"".equals(startTime)) && (endTime != null) && (!"".equals(endTime))) {
        sql.append("and a.att_date between '" + startTime + "' and '" + endTime + "' ");
      }
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next())
        num = this.rs.getInt(1);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return num;
  }

  public List<UserList> getAllUser(int pageSize, int startRow, int schId, String name, String ssn)
  {
    List attlist = new ArrayList();
    UserList ul = null;
    try {
      StringBuffer sql1 = new StringBuffer("and USERID not in (select top " + startRow + " USERID from USERINFO where DEFAULTDEPTID not in (1,-1) and DEFAULTDEPTID=" + schId + " ");

      StringBuffer sql = new StringBuffer("select top " + pageSize + " * from USERINFO where DEFAULTDEPTID not in (1,-1) and DEFAULTDEPTID=" + schId + " ");
      String sqls = "";
      if ((name != null) && (!"".equals(name))) {
        sqls = "and Name like '%" + name + "%' ";
        sql.append(sqls);
        sql1.append(sqls);
      }
      if ((ssn != null) && (!"".equals(ssn))) {
        sqls = "and Badgenumber = '" + ssn + "' ";
        sql.append(sqls);
        sql1.append(sqls);
      }
      sql.append(sql1).append("order by USERID desc) order by USERID desc");
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      System.out.println(sql.toString());
      while (this.rs.next()) {
        ul = new UserList();
        ul.setUserId(this.rs.getInt("USERID"));
        ul.setName(this.rs.getString("Name"));
        ul.setGender(this.rs.getString("Gender"));
        ul.setBadgenumber(this.rs.getString("Badgenumber"));
        ul.setSubdept(this.rs.getString("SubDept"));
        ul.setOaId(this.rs.getInt("tea_id"));
        ul.setPostId(this.rs.getInt("post_id"));
        ul.setSchId(this.rs.getInt("DEFAULTDEPTID"));
        ul.setStatus(this.rs.getInt("status"));
        attlist.add(ul);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return attlist;
  }
  
  public List<UserList> getAllUserSecond(int pageSize, int startRow, int schId, String name, String ssn)
  {
    List attlist = new ArrayList();
    UserList ul = null;
    try {
      StringBuffer sql1 = new StringBuffer("USERID not in (select top " + startRow + " USERID from USERINFO    ");

      StringBuffer sql = new StringBuffer("select top " + pageSize + " * from USERINFO  where ");
      String sqls = "";
      if ((name != null) && (!"".equals(name))) {
        sqls = "Name like '%" + name + "%' ";
        sql.append(sqls);
        sql1.append(sqls);
      }
      if ((ssn != null) && (!"".equals(ssn))) {
        sqls = "and Badgenumber = '" + ssn + "' ";
        sql.append(sqls);
        sql1.append(sqls);
      }
      sql.append(sql1).append("order by cast(badgenumber as int) desc) order by cast(badgenumber as int) desc");
      System.out.println(sql.toString());
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      
      while (this.rs.next()) {
        ul = new UserList();
        ul.setUserId(this.rs.getInt("USERID"));
        ul.setName(this.rs.getString("Name"));
        ul.setGender(this.rs.getString("Gender"));
        ul.setBadgenumber(this.rs.getString("Badgenumber"));
        ul.setSubdept(this.rs.getString("SubDept"));
        ul.setOaId(this.rs.getInt("tea_id"));
        ul.setPostId(this.rs.getInt("post_id"));
        ul.setSchId(this.rs.getInt("DEFAULTDEPTID"));
        ul.setStatus(this.rs.getInt("status"));
        attlist.add(ul);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return attlist;
  }

  public int getCountUser(int schId, String name, String ssn)
  {
    int num = 0;
    try {
      StringBuffer sql = new StringBuffer("select COUNT(*) from USERINFO where DEFAULTDEPTID not in (1,-1) and DEFAULTDEPTID=" + schId + " ");
      if ((name != null) && (!"".equals(name))) {
        sql.append("and Name like '%" + name + "%' ");
      }
      if ((ssn != null) && (!"".equals(ssn))) {
        sql.append("and Badgenumber = '" + ssn + "' ");
      }
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next())
        num = this.rs.getInt(1);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return num;
  }
  public int getSchoolInfo(String schoolname)
  {
	  if(schoolname!=null&&schoolname.indexOf("罗湖旗舰校区")>0)
	  {
		  schoolname="罗湖校区";
	  }
	  if(schoolname!=null&&schoolname.indexOf("竞争力一对一")>0)
	  {
		  schoolname="竞争力分校";
	  }
	  if(schoolname!=null&&schoolname.indexOf("沙头角分校")>0)
	  {
		  schoolname="沙头角校区";
	  }
	  
	  int num=0;
	  try {
	      StringBuffer sql = new StringBuffer("select deptid from DEPARTMENTS where deptname like  '%"+schoolname+"%'");
	      this.conn = getConn();
	      this.pstmt = this.conn.prepareStatement(sql.toString());
	      this.rs = this.pstmt.executeQuery();
	      while (this.rs.next())
	        num = this.rs.getInt(1);
	    }
	    catch (Exception e) {
	      e.printStackTrace();
	    } finally {
	      closeAll(this.conn, this.pstmt, this.rs);
	    }
	    if(num!=0)
	    {
	    	return num;
	    }
	    else
	    {
	    	return 16;//默认放总部
	    }
  }
  public int getCountUser( String name, String ssn)
  {
    int num = 0;
    try {
      StringBuffer sql = new StringBuffer("select COUNT(*) from USERINFO where DEFAULTDEPTID not in (1,-1)  ");
      if ((name != null) && (!"".equals(name))) {
        sql.append("and Name like '%" + name + "%' ");
      }
      if ((ssn != null) && (!"".equals(ssn))) {
        sql.append("and Badgenumber = '" + ssn + "' ");
      }
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next())
        num = this.rs.getInt(1);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return num;
  }

  public int upUser(UserList ul)
  {
    StringBuffer sql = new StringBuffer("update USERINFO set status=? where USERID=?");
    String[] params = { Integer.toString(ul.getStatus()), Integer.toString(ul.getUserId()) };
    return executeSQL(sql.toString(), params);
  }

  public int upOAID(UserList ul)
  {
	  int num=getSchoolInfo(ul.getSchoolname());
    StringBuffer sql = new StringBuffer("update USERINFO set tea_id=?,post_id=?,SubDept=?,defaultdeptid=?,IsZong=0,status=? where USERID=?");
    String[] params = { Integer.toString(ul.getOaId()), Integer.toString(ul.getPostId()), ul.getSubdept(), Integer.toString(num),Integer.toString(ul.getStatus()), Integer.toString(ul.getUserId()) };
    return executeSQL(sql.toString(), params);
  }

  public int updateAtt(Attendance att)
  {
    StringBuffer sql = new StringBuffer("update Attendance set att_latetime=?,att_earlytime=?,att_state=?,att_desc=?,att_updatestate=1,att_confirm=5 where att_id=?");
    String[] params = { Integer.toString(att.getAttlatetime()), Integer.toString(att.getAttearlytime()), Integer.toString(att.getAttstate()), att.getAttdesc(), Integer.toString(att.getAttid()) };
    return executeSQL(sql.toString(), params);
  }
  public int upTime(Attendance att) {
    StringBuffer sql = new StringBuffer("update Attendance set att_worktime=?,att_closingtime=? where att_id=?");
    String[] params = { att.getAttworktime(), att.getAttclosingtime(), Integer.toString(att.getAttid()) };
    return executeSQL(sql.toString(), params);
  }

  public int upVelocity(Attendance att)
  {
    StringBuffer sql = new StringBuffer("update Attendance set att_updatestate=1,att_confirm=5 where att_id=?");
    String[] params = {Integer.toString( att.getAttid()) };
    return executeSQL(sql.toString(), params);
  }
  public int upVelo(Attendance att) {
    StringBuffer sql = new StringBuffer("update Attendance set att_updatestate=1,att_confirm=5 where att_id=?");
    String[] params = { Integer.toString(att.getAttid()) };
    return executeSQL(sql.toString(), params);
  }

  public int upConfirm(Attendance at)
  {
    StringBuffer sql = new StringBuffer("update Attendance set att_state=1,att_latetime=0,att_earlytime=0,att_updatestate=2,att_confirm=3 where att_id=?");
    String[] params = { Integer.toString(at.getAttid()) };
    return executeSQL(sql.toString(), params);
  }

  public int updateConfirm(Attendance at, String s)
  {
    StringBuffer sql = new StringBuffer("update Attendance set att_confirm=?,att_updatestate=? where att_id in (" + s + ")");
    String[] params = { Integer.toString(at.getAttconfirm()),Integer.toString(at.getAttupdatestate()) };
    return executeSQL(sql.toString(), params);
  }
  public int updateConfirm(Attendance at) {
    StringBuffer sql = new StringBuffer("update Attendance set att_confirm=?,att_updatestate=? where att_id=?");
    String[] params = { Integer.toString(at.getAttconfirm()), Integer.toString(at.getAttupdatestate()), Integer.toString(at.getAttid()) };
    return executeSQL(sql.toString(), params);
  }

  public int updateApp(Appeal app)
  {
    StringBuffer sql = new StringBuffer("update appeal set app_status=? where app_id=?");
    String[] params = { Integer.toString(app.getStatus()), Integer.toString(app.getAppid() )};
    return executeSQL(sql.toString(), params);
  }

  public int updateAppType(Appeal app) {
    StringBuffer sql = new StringBuffer("update appeal set app_replytime=getdate(), app_type=? where app_id=?");
    String[] params = { Integer.toString(app.getType()), Integer.toString(app.getAppid()) };
    return executeSQL(sql.toString(), params);
  }

  public int updateAppReply(Appeal app) {
    StringBuffer sql = new StringBuffer("update appeal set app_reply=?,app_replytime=getdate(),app_type=? where app_id=?");
    String[] params = { app.getReply(), Integer.toString(app.getType()), Integer.toString(app.getAppid()) };
    return executeSQL(sql.toString(), params);
  }

  public Attendance searchAttById(int attId)
  {
    Attendance att = null;
    try {
      StringBuffer sql = new StringBuffer("select u.Name,d.DEPTNAME,a.* from USERINFO u,Attendance a,DEPARTMENTS d where u.USERID=a.att_user_id and u.DEFAULTDEPTID=d.DEPTID and a.att_id=" + attId);
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        att = new Attendance();
        att.setAttid(this.rs.getInt("att_id"));
        att.setAstuserid(this.rs.getInt("att_user_id"));
        att.setName(this.rs.getString("Name"));
        att.setDeptname(this.rs.getString("DEPTNAME"));
        att.setAttssn(this.rs.getString("att_ssn"));
        att.setAttdate(this.rs.getString("att_date"));
        att.setAttworktime(this.rs.getString("att_worktime"));
        att.setAttclosingtime(this.rs.getString("att_closingtime"));
        att.setAttstate(this.rs.getInt("att_state"));
        att.setAttlatetime(this.rs.getInt("att_latetime"));
        att.setAttearlytime(this.rs.getInt("att_earlytime"));
        att.setAttconfirm(this.rs.getInt("att_confirm"));
        att.setAttdesc(this.rs.getString("att_desc"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return att;
  }

  public AppealTime searchAppById(int appId)
  {
    AppealTime app = null;
    try {
      StringBuffer sql = new StringBuffer("select * from appeal a,Attendance b where a.app_att_id=b.att_id and a.app_id=" + appId);
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        app = new AppealTime();
        app.setAppid(this.rs.getInt("app_id"));
        app.setContent(this.rs.getString("app_content"));
        app.setRecipientid(this.rs.getInt("app_recipientid"));
        app.setCreatetime(this.rs.getString("app_createtime").substring(0, 19));
        app.setSendname(this.rs.getString("app_sendname"));
        app.setStatus(this.rs.getInt("app_status"));
        app.setType(this.rs.getInt("app_type"));
        app.setReply(this.rs.getString("app_reply"));
        app.setAttdate(this.rs.getString("att_date"));
        app.setAttworktime(this.rs.getString("att_worktime"));
        app.setAttclosingtime(this.rs.getString("att_closingtime"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return app;
  }

  public int findRoleUser(int teaId)
  {
    int num = 0;
    try {
      StringBuffer sql = new StringBuffer("select COUNT(*) from att_personnew where per_user_id=" + teaId);
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next())
        num = this.rs.getInt(1);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return num;
  }

  public int findRole(int userId)
  {
    int num = 0;
    try {
      StringBuffer sql = new StringBuffer("select COUNT(*) from att_person where userid=" + userId);
      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next())
        num = this.rs.getInt(1);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return num;
  }

  public List<Shensu> List(int userId, int sta)
  {
    List sl = new ArrayList();
    try {
      StringBuffer sql = new StringBuffer("select p.SubDept,p.Title,p.per_teaId from att_person p where p.IsZong in (select o.IsZong from userinfo o where o.userid=" + 
        userId + ") ");
      if (sta != 0)
        sql.append(" and p.Grade in (2,3) ");
      else {
        sql.append(" and p.Grade=1 ");
      }
      sql.append("order by userid");

      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        Shensu su = new Shensu();
        su.setOaId(Integer.valueOf(this.rs.getInt("per_teaId")));
        su.setuName(this.rs.getString("Title"));
        su.setSubDept(this.rs.getString("SubDept"));
        sl.add(su);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return sl;
  }

  public List<Detail> getSchool(int userId, String dt)
  {
    List sl = new ArrayList();
    Detail detail = null;
    try {
      StringBuffer sql = new StringBuffer("select distinct c.CheckTime,s.schName from v_CheckInOut c,school s where c.SENSORID=s.schId and c.CheckDate='" + 
        dt + "' and USERID=" + userId + " " + 
        "order by c.CheckTime");

      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next()) {
        detail = new Detail();
        detail.setDatetime(this.rs.getString(1));
        detail.setSchName(this.rs.getString(2));
        sl.add(detail);
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return sl;
  }

  public boolean findAttId(int attId) {
    boolean arg = false;
    int num = 0;
    try {
      StringBuffer sql = new StringBuffer("select COUNT(app_id) from appeal where app_type in (0,1) and app_att_id =" + 
        attId);

      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next())
        num = this.rs.getInt(1);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    if (num != 0) {
      arg = true;
    }
    return arg;
  }

  public int showTip(int teaId)
  {
    int num = 0;
    try {
      StringBuffer sql = new StringBuffer("select COUNT(app_id) from appeal where app_type=0 and app_recipientid=" + teaId);

      this.conn = getConn();
      this.pstmt = this.conn.prepareStatement(sql.toString());
      this.rs = this.pstmt.executeQuery();
      while (this.rs.next())
        num = this.rs.getInt(1);
    }
    catch (Exception e) {
      e.printStackTrace();
    } finally {
      closeAll(this.conn, this.pstmt, this.rs);
    }
    return num;
  }
}